unit unitalumnos;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
  MaskEdit, DBGrids, ExtCtrls, unitmainform, sqldb, db, unitadmactividades;

type

  { TFormAlumnos }

  TFormAlumnos = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    CheckBox3: TCheckBox;
    CheckBox4: TCheckBox;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Edit1: TEdit;
    Edit2: TEdit;
    Edit3: TEdit;
    Edit5: TEdit;
    Edit6: TEdit;
    Edit7: TEdit;
    Edit8: TEdit;
    Image1: TImage;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Edit4: TMaskEdit;
    Label9: TLabel;
    SQLQuery1: TSQLQuery;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure DBGrid1CellClick(Column: TColumn);
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
    procedure FormShow(Sender: TObject);
    procedure ordenar_grid();
    procedure FormCreate(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  FormAlumnos: TFormAlumnos;

implementation

{$R *.lfm}

{ TFormAlumnos }

procedure TFormAlumnos.ordenar_grid();
begin
  if SQLQuery1.Active = true then
    SQLQuery1.Close;

  SQLQuery1.SQL.Text := 'select * from vw_almdpt where alm_dni = ''' + Edit1.Text + ''' order by dpt_nombre';

  SQLQuery1.Open;

  DBGrid1.Columns[0].Visible := false;
  DBGrid1.Columns[1].Visible := false;
  DBGrid1.Columns[2].Visible := false;
  DBGrid1.Columns[3].Visible := false;
  DBGrid1.Columns[4].Visible := false;
  DBGrid1.Columns[9].Visible := false;
  DBGrid1.Columns[10].Visible := false;
  DBGrid1.Columns[11].Visible := false;
  DBGrid1.Columns[12].Visible := false;
  DBGrid1.Columns[13].Visible := false;
  DBGrid1.Columns[14].Visible := false;

  DBGrid1.Columns[5].Width := 350;
  DBGrid1.Columns[6].Width := 150;
  DBGrid1.Columns[7].Width := 300;
  DBGrid1.Columns[8].Width := 250;

  DBGrid1.Columns[5].Title.Caption := 'Actividad';
  DBGrid1.Columns[6].Title.Caption := 'Edad / Sexo';
  DBGrid1.Columns[7].Title.Caption := 'Horarios';
  DBGrid1.Columns[8].Title.Caption := 'Espacio';
end;

procedure TFormAlumnos.Button1Click(Sender: TObject);
var
  QueryString : string;

begin
  Edit1.Text := uppercase(Edit1.Text);
  Edit2.Text := uppercase(Edit2.Text);
  Edit3.Text := uppercase(Edit3.Text);
  Edit5.Text := uppercase(Edit5.Text);
  Edit6.Text := uppercase(Edit6.Text);

  if FormAlumnos.Caption = 'Alta de Alumno' then
  begin
    QueryString := 'insert into alumno(alm_dni, alm_apellido, alm_nombre, alm_fecha_nac, alm_direccion, '
      + 'alm_barrio, alm_telefono, alm_celular, alm_presento_certificado, alm_presento_fotoc_dni, alm_presento_planilla) '
      + 'values(''' + Edit1.Text + ''', ''' + Edit2.text + ''', ''' + Edit3.Text + ''', ''' + Edit4.Text + ''', ''' + Edit5.Text
      + ''', ''' + Edit6.Text + ''', ''' + Edit7.Text + ''', ''' + Edit8.Text + ''', ''' + booltostr(CheckBox1.Checked, True) + ''', '''
      + booltostr(CheckBox2.Checked, True) + ''', ''' + booltostr(CheckBox3.Checked, True) + ''')';

    MainForm.PQConnection1.ExecuteDirect('BEGIN WORK;');
    MainForm.PQConnection1.ExecuteDirect(QueryString);
    MainForm.PQConnection1.ExecuteDirect('COMMIT WORK;');

    Button1.Enabled := false;

    ordenar_grid();
  end
  else
  if FormAlumnos.Caption = 'Modificacion de Alumno' then
  begin
    QueryString := 'update alumno set alm_apellido = ''' + Edit2.Text + ''', alm_nombre = ''' + Edit3.Text + ''', alm_fecha_nac = '''
      + Edit4.Text + ''', alm_direccion = ''' + Edit5.Text + ''', alm_barrio = ''' + Edit6.Text + ''', alm_telefono = ''' + Edit7.Text
      + ''', alm_celular = ''' + Edit8.Text + ''', alm_presento_certificado = ''' + booltostr(CheckBox1.Checked, True)
      + ''', alm_presento_fotoc_dni = ''' + booltostr(CheckBox2.Checked, True) + ''', alm_presento_planilla = '''
      + booltostr(CheckBox3.Checked, True) + ''', alm_activo = ''' + booltostr(CheckBox4.Checked, True)
      + '''where alm_dni = ''' + Edit1.Text + '''';

    MainForm.PQConnection1.ExecuteDirect('BEGIN WORK;');
    MainForm.PQConnection1.ExecuteDirect(QueryString);
    MainForm.PQConnection1.ExecuteDirect('COMMIT WORK;');
  end;
end;

procedure TFormAlumnos.Button2Click(Sender: TObject);
var
  QueryString : string;
begin
  FormAdmActividades.Caption := 'Seleccione Actividad';
  FormAdmActividades.ShowModal;

  QueryString := 'insert into deporte_alumno(almdpt_alm, almdpt_dpt) values(' + Edit1.Text + ', '
    + FormAdmActividades.dpt_cdg + ')';
  try
    MainForm.PQConnection1.ExecuteDirect('BEGIN WORK;');
    MainForm.PQConnection1.ExecuteDirect(QueryString);
    MainForm.PQConnection1.ExecuteDirect('COMMIT WORK;');

    QueryString := 'insert into asistencia(ast_alumno, ast_deporte, ast_tipo) values(' + Edit1.Text + ', '
      + FormAdmActividades.dpt_cdg + ', ''ENTRADA'')';

    MainForm.PQConnection1.ExecuteDirect('BEGIN WORK;');
    MainForm.PQConnection1.ExecuteDirect(QueryString);
    MainForm.PQConnection1.ExecuteDirect('COMMIT WORK;');

    QueryString := 'insert into asistencia(ast_alumno, ast_deporte, ast_tipo) values(' + Edit1.Text + ', '
      + FormAdmActividades.dpt_cdg + ', ''SALIDA'')';

    MainForm.PQConnection1.ExecuteDirect('BEGIN WORK;');
    MainForm.PQConnection1.ExecuteDirect(QueryString);
    MainForm.PQConnection1.ExecuteDirect('COMMIT WORK;');
  except
    on E: Exception do
    begin
      MainForm.SQLTransaction1.Rollback;
      MainForm.SQLTransaction1.EndTransaction;
      ShowMessage('El alumno ya realiza esta actividad');
    end;
  end;

  ordenar_grid();
end;

procedure TFormAlumnos.Button3Click(Sender: TObject);
begin
  ShowMessage('Click en la actividad a eliminar...');
end;

procedure TFormAlumnos.Button4Click(Sender: TObject);
begin
  close();
end;

procedure TFormAlumnos.DBGrid1CellClick(Column: TColumn);
var
  QueryString : string;

begin
  queryString := 'delete from deporte_alumno where almdpt_cdg = ' + SQLQuery1.FieldByName('almdpt_cdg').AsString;
  MainForm.PQConnection1.ExecuteDirect('BEGIN WORK;');
  MainForm.PQConnection1.ExecuteDirect(QueryString);
  MainForm.PQConnection1.ExecuteDirect('COMMIT WORK;');

  ordenar_grid();
end;

procedure TFormAlumnos.FormClose(Sender: TObject; var CloseAction: TCloseAction
  );
begin
  if SQLQuery1.Active = true then
    SQLQuery1.Close;
end;

procedure TFormAlumnos.FormShow(Sender: TObject);
begin
  if FormAlumnos.Caption = 'Modificacion de Alumno' then
    ordenar_grid();
end;

procedure TFormAlumnos.FormCreate(Sender: TObject);
begin
  SQLQuery1.DataBase := MainForm.PQConnection1;
  SQLQuery1.Transaction := MainForm.SQLTransaction1;
end;

end.

